﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PPI.Data;

namespace PPIDataAccess
{
    public class DReport
    {
        public static DataSet GetReportVisitsByTop(int projectId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_VisitsbyTop", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(
                    DBHelper.CreateIntParameter("@ProjectId", ParameterDirection.Input, false, projectId));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_VisitsbyTop", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportPolesByAge(int projectId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_PolesByAge", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(
                    DBHelper.CreateIntParameter("@ProjectId", ParameterDirection.Input, false, projectId));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_PolesByAge", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportVisitByWeekContact(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_VisitsByWeekAndContact", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_VisitsByWeekAndContact", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportPoleByWeekContact(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_PolesByWeekAndContact", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_PolesByWeekAndContact", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportInspectionByWeekAndContact(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_InspectionsByWeekAndContact", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_InspectionsByWeekAndContact", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportInspectionTypeByWeekAndInspector(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_InspectionTypesByWeekAndInspector", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_InspectionTypesByWeekAndInspector", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportInspectionTypeByWeekAndInspector(int projectId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_InspectionTypesByWeekAndInspectorByProject", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@projectID", ParameterDirection.Input, false, projectId));
   
                DataSet ds = new DataSet();
                da.Fill(ds);
   
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_InspectionTypesByWeekAndInspectorByProject", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportDistributionInspectionTypeByWeekAndInspector(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_DistributionInspectionTypesByWeekAndInspector", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_DistributionInspectionTypesByWeekAndInspector", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportProjectSummary(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_ProjectSummary", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_ProjectSummary", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportProjectSummary(int projectId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_ProjectSummaryByProject", DBHelper.GetConnection(connectionKey));

                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@projectId", ParameterDirection.Input, false, projectId));

                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_ProjectSummary", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportDistributionInspectionProjectSummary(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_DistributionInspectionProjectSummary", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_DistributionInspectionProjectSummary", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportProjectSummary_Issue(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_ProjectSummary_Isssue", DBHelper.GetConnection(connectionKey));

                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_ProjectSummary_Isssue", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportProjectSummary_Issue(int projectId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_ProjectSummary_IsssueByProject", DBHelper.GetConnection(connectionKey));

                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@projectId", ParameterDirection.Input, false, projectId));
                
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_ProjectSummary_Isssue", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportDistributionInspectionProjectSummary_Issue(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_DistributionInspection_ProjectSummary_Isssue", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_DistributionInspection_ProjectSummary_Isssue", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }
        

        public static DataSet GetReportProjectSummary_Budget(int projectId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_ProjectSummary_BudgetByProject", DBHelper.GetConnection(connectionKey));

                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@projectId", ParameterDirection.Input, false, projectId));

                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_ProjectSummary_Budget", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportProjectSummary_Budget(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_ProjectSummary_Budget", DBHelper.GetConnection(connectionKey));

                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_ProjectSummary_Budget", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportDistributionInspectionProjectSummary_Budget(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_DistributionInspection_ProjectSummary_Budget", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_DistributionInspection_ProjectSummary_Budget", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportLineProblems(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_LineProblemsByResultAndNote", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_LineProblemsByResultAndNote", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportIssueByResult(string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_IssueByResult", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_IssueByResult", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportAssetDescription(Int32 assetId,string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_InformationAsset", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, false, assetId));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_InformationAsset", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportAssetDescriptionGPS(Int32 assetId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_InformationAsset_Gps", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, false, assetId));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_InformationAsset_Gps", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }

        public static DataSet GetReportAssetDescriptionInspection(Int32 assetId, string connectionKey)
        {
            SqlDataAdapter da = null;
            try
            {
                da = new SqlDataAdapter("up_Report_InformationAsset_Inspections", DBHelper.GetConnection(connectionKey));
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(DBHelper.CreateIntParameter("@AssetId", ParameterDirection.Input, false, assetId));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Error executing up_Report_InformationAsset_Inspections", ex);
            }
            finally
            {
                if (da != null) da.SelectCommand.Connection.Close();
            }
        }
    }
}
